package Server.DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
 * implement all register query handler
 * */
public class RegQuery extends Query{
/**
 * constructor RegQuery
 */
	public RegQuery() {
		super();
	}
	/**
	 * check if students pass the course
	 * @param course
	 * @param student
	 * @return 1 if students pass the course
	 *       else 0 
	 * @throws SQLException
	 */
	public Boolean isPassCourse(Course course, Student student) throws SQLException{
		setQuery("SELECT * FROM reg WHERE idCourse='"
				+course.getIdCourse()+"' AND idUser='"+student.getIdUser()+"'");
		ResultSet rs = execQuery();
		if (rs.next()) return true;
		else return false;
	}
	/**
	 * 
	 * @param student
	 * @param idLesson
	 * @return 1 students register to lesson
	 *         0 if he already pass the course
	 * @throws SQLException
	 */
	public Boolean regStudentToLesson(Student student, int idLesson) throws SQLException{
		int idCourse = new LessonQuery().getCourseId(idLesson);
		int idReg = new Info().getNewRegId();
		ArrayList<Course> array = new PreCourseQuery().getPreCourse(idCourse);
		for (Course course: array)
			if (!isPassCourse(course, student)) return false; //check if he already pass the course than return 0
		addReg(new Reg(idReg, student.getIdUser(), idLesson, idCourse, 0, 0));
		return true;
	}
	/**
	 * register Student To Lesson By Request
	 * @param user
	 * @param idLesson
	 * @throws SQLException
	 */
	public void regStudentToLessonByRequest(User user, int idLesson) throws SQLException{
		int idCourse = new LessonQuery().getCourseId(idLesson);
		int idReg = new Info().getNewRegId();
		addReg(new Reg(idReg, user.getIdUser(), idLesson, idCourse, 1, 0));
	}
	/**
	 * add a new reg to table
	 * @param reg
	 * @throws SQLException
	 */
	public void addReg(Reg reg) throws SQLException{
		setQuery("INSERT INTO reg(idReg, idUser, idLesson, idCourse, approved, grade) VALUES(?,?,?,?,?,?)");
		PreparedStatement query = getPS();
		query.setInt(1, reg.getIdReg());
		query.setInt(2, reg.getIdUser());
		query.setInt(3, reg.getIdLesson());
		query.setInt(4, reg.getIdcourse());
		query.setInt(5, reg.getApp());
		query.setInt(6, reg.getGrade());
		query.executeUpdate();
		query.close();
	}
	/**
	 * remove request from table
	 * @param request
	 * @throws SQLException
	 */
	public void removeReg(Request request) throws SQLException{
		setQuery("UPDATE reg SET approved=? WHERE idLesson=? AND idUser=?");
		PreparedStatement query = getPS();
		query.setInt(1, 2);
		query.setInt(2, request.getLesson().getIdLesson());
		query.setInt(3, request.getUser().getIdUser());
		query.executeUpdate();
		query.close();
	}
	/**
	 * set assesment form attribute
	 * @param iduser
	 * @param idLesson
	 * @param grade
	 * @param commant
	 * @throws SQLException
	 */
	public void assesmentForm(int iduser, int idLesson, int grade, String commant) throws SQLException{
		setQuery("UPDATE reg SET grade=?, commant=? WHERE idLesson=? AND idUser=? AND approved!='2'");
		PreparedStatement query = getPS();
		query.setInt(1, grade);
		query.setString(2, commant);
		query.setInt(3, idLesson);
		query.setInt(4, iduser);
		query.executeUpdate();
		query.close();
	}
	
	/**
	 * 
	 * @param idReg
	 * @return register data
	 * @throws SQLException
	 */
	public Reg getReg(int idReg) throws SQLException{
		setQuery("SELECT * FROM reg WHERE idReg='"+idReg+"'");
		ResultSet rs = execQuery();
		if (rs.next()){
			Course course = new CourseQuery().getCourseById(rs.getInt(4));
			return new Reg(rs.getInt(1), rs.getInt(2),rs.getInt(3),course,
					rs.getInt(5), rs.getInt(6),rs.getString(7));
		}
		return null;
	}
	/**
	 * 
	 * @param idStudent
	 * @return register data for students
	 * @throws SQLException
	 */
	public ArrayList<Reg> getStudentReg(int idStudent) throws SQLException{
		ArrayList<Reg> array = new ArrayList<Reg>();
		setQuery("SELECT idReg FROM reg WHERE idUser='"+idStudent+"'");
		ResultSet rs = execQuery();
		while (rs.next()){
			Reg reg = getReg(rs.getInt(1));
			if (reg.getApp()!=2)
				array.add(reg);
		}
		return array;
	}
	/**
	 * 
	 * @param idUser
	 * @param idSemester
	 * @return register student for a semester
	 * @throws SQLException
	 */
	public ArrayList<Reg> getStudentRegInSemester(int idUser, int idSemester) throws SQLException{
		ArrayList<Reg> array = new ArrayList<Reg>();
		setQuery("SELECT reg.idReg FROM lesson, reg "+
				"WHERE lesson.idlesson=reg.idlesson AND reg.iduser='"+idUser+"' " +
						"AND lesson.idsemester='"+idSemester+"'");
		ResultSet rs = execQuery();
		while (rs.next())
				array.add(getReg(rs.getInt(1)));
		return array;
	}
	/**
	 * 
	 * @param idStudent
	 * @return all students registers
	 * @throws SQLException
	 */
	public ArrayList<Reg> getAllStudentReg(int idStudent) throws SQLException{
		ArrayList<Reg> array = new ArrayList<Reg>();
		setQuery("SELECT idReg FROM reg WHERE idUser='"+idStudent+"'");
		ResultSet rs = execQuery();
		while (rs.next()) array.add(getReg(rs.getInt(1)));
		return array;
	}
	/**
	 * 
	 * @param idLesson
	 * @return reg for a lesson
	 * @throws SQLException
	 */
	public ArrayList<Reg> getStudentRegToLesson(int idLesson) throws SQLException{
		ArrayList<Reg> array = new ArrayList<Reg>();
		setQuery("SELECT idReg FROM reg WHERE idLesson='"+idLesson+"'");
		ResultSet rs = execQuery();
		while (rs.next()) array.add(getReg(rs.getInt(1)));
		return array;
	}
	/**
	 * 
	 * @return reg table
	 * @throws SQLException
	 */
	public ResultSet getTable() throws SQLException{
		setQuery("SELECT * FROM reg");
		return execQuery();
	}
}
